package com.yss.common.core.utils.sql;

import cn.hutool.core.date.DateTime;
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.parser.*;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.yss.common.core.enums.YssDataBaseEnum;
import com.yss.common.core.exception.BusinessException;
import com.yss.common.core.exception.UtilException;
import com.yss.common.core.utils.StringUtils;

import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.util.*;

/**
 * sql操作工具类
 */

public class SqlUtil {
    /**
     * 定义常用的 sql关键字
     */
    public static String SQL_REGEX = "and |extractvalue|updatexml|exec |insert |select |delete |update |drop |count |chr |mid |master |truncate |char |declare |or |+|user()";

    /**
     * 仅支持字母、数字、下划线、空格、逗号、小数点（支持多个字段排序）
     */
    public static String SQL_PATTERN = "[a-zA-Z0-9_\\ \\,\\.]+";

    /**
     * 检查字符，防止注入绕过
     */
    public static String escapeOrderBySql(String value) {
        if (StringUtils.isNotEmpty(value) && !isValidOrderBySql(value)) {
            throw new UtilException("参数不符合规范，不能进行查询");
        }
        return value;
    }

    /**
     * 验证 order by 语法是否符合规范
     */
    public static boolean isValidOrderBySql(String value) {
        return value.matches(SQL_PATTERN);
    }

    /**
     * SQL关键字检查
     */
    public static void filterKeyword(String value) {
        if (StringUtils.isEmpty(value)) {
            return;
        }
        String[] sqlKeywords = StringUtils.split(SQL_REGEX, "\\|");
        for (String sqlKeyword : sqlKeywords) {
            if (StringUtils.indexOfIgnoreCase(value, sqlKeyword) > -1) {
                throw new UtilException("参数存在SQL注入风险");
            }
        }
    }

    /**
     * 通过反射拼接生成SQL语句
     *
     * @param tableName
     * @param list
     * @param clazz
     * @return
     * @throws Exception
     */
    public static String getSqlByList(String tableName, List list, Class clazz) {
        StringBuffer insertSql = new StringBuffer();
        try {
            Field[] fields = clazz.getDeclaredFields();
            for (Object obj : list) {
                insertSql.append("INSERT INTO " + tableName).append("(");
                StringJoiner bufferFieldInsert = new StringJoiner(",");
                StringJoiner bufferFieldValueInsert = new StringJoiner(",");
                for (Field field : fields) {
                    if (StringUtils.isNull(field)) {
                        continue;
                    }
                    field.setAccessible(true);
                    TableId tableId = field.getAnnotation(TableId.class);
                    if (StringUtils.isNotNull(tableId)) {
                        bufferFieldInsert.add(tableId.value());
                        if (StringUtils.isNotNull(field.get(obj))) {
                            bufferFieldValueInsert.add("'" + field.get(obj).toString() + "'");
                        } else {
                            bufferFieldValueInsert.add(null);
                        }
                        field.setAccessible(false);
                        continue;
                    }
                    TableField tableField = field.getAnnotation(TableField.class);
                    if (StringUtils.isNull(tableField) || StringUtils.isEmpty(tableField.value()) || !tableField.exist()) {
                        field.setAccessible(false);
                        continue;
                    }
                    if (StringUtils.oneCase(tableField.value(), "RW_DB_DATABASE_TYPE,RW_DB_ID")) {
                        field.setAccessible(false);
                        continue;
                    }
                    bufferFieldInsert.add(tableField.value());
                    // 判断其类型进行赋值操作
                    if (field.getType().getName().equals(String.class.getName())) {
                        if (StringUtils.isNotNull(field.get(obj))) {
                            String value = field.get(obj).toString();
                            if (value.indexOf("'") > -1) {
                                value = value.replaceAll("\\'", "\\'\\'");
                            }
                            bufferFieldValueInsert.add("'" + value + "'");
                        } else {
                            bufferFieldValueInsert.add(null);
                        }
                    } else if (field.getType().getName().equals(int.class.getName())
                            || field.getType().getName().equals(Integer.class.getName())
                            || field.getType().getName().equals(BigDecimal.class.getName())
                            || field.getType().getName().equals(Long.class.getName())
                    ) {
                        if (StringUtils.isNotNull(field.get(obj))) {
                            bufferFieldValueInsert.add(field.get(obj).toString());
                        } else {
                            bufferFieldValueInsert.add(null);
                        }
                    } else if (field.getType().getName().equals(Date.class.getName())
                            || field.getType().getName().equals(DateTime.class.getName())
                            || field.getType().getName().equals(Timestamp.class.getName())
                    ) {
                        if (StringUtils.isNotNull(field.get(obj))) {
                            bufferFieldValueInsert.add("SYSDATE");
                        } else {
                            bufferFieldValueInsert.add(null);
                        }
                    } else if (field.getType().getName().equals(Boolean.class.getName())) {

                    }
                    field.setAccessible(false);
                }
                insertSql.append(bufferFieldInsert).append(") VALUES (").append(bufferFieldValueInsert).append(");").append("\r\n");
            }
        } catch (Exception ex) {
            throw new UtilException("生成SQL语句失败" + ex.getMessage());
        }
        return insertSql.toString();
    }

    // 系统的数据库类型，返回duid的数据库类型
    public static String getDbType(String databaseType) {
        YssDataBaseEnum baseEnum = YssDataBaseEnum.getEnum(databaseType);
        switch (baseEnum) {
            case ORACLE:
                return DbType.ORACLE.getDb();
            case MYSQL:
                return DbType.MYSQL.getDb();
            case DM:
                return DbType.DM.getDb();
            case POSTGRESQL:
                return DbType.POSTGRE_SQL.getDb();
            default:
                return DbType.ORACLE.getDb();
        }
    }

    /**
     * 脚本提交检查SQL语句语法是否正确（提交SQL脚本的时候，检查最后每个语句要分号隔开）
     *
     * @param sql
     * @return
     */
    public static List<SQLStatement> validatedSqlScript(String sql, String dbType) {
        List<SQLStatement> stmtList = null;
        try {
            if (StringUtils.isEmpty(sql)) {
                throw new BusinessException("没有获取SQL脚本信息，请检查！");
            }
            checLexerToken(sql);
            //格式化输出
            stmtList = SQLUtils.parseStatements(sql, dbType);
            if (StringUtils.isEmpty(stmtList)) {
                throw new BusinessException("没有解析出你提交的SQL脚本信息，请检查！");
            }
            for (SQLStatement stmt : stmtList) {
                if (null == stmt || StringUtils.isEmpty(stmt.toString())) {
                    throw new BusinessException("没有解析出你提交的SQL脚本信息，请检查！");
                }
                if (!stmt.toString().endsWith(";")) {
                    throw new BusinessException("SQL脚本都需要已英文分号结尾，请检查！");
                }
                SQLStatementParser statementParser = SQLParserUtils.createSQLStatementParser(stmt.toString(), dbType);
                statementParser.parseStatement();
            }
        } catch (Exception ex) {
            throw new BusinessException("解析SQL出现语法错误，请检查！" + ex.getMessage());
        }
        return stmtList;
    }

    /**
     * 检查SQL语句语法是否正确（
     *
     * @param sql
     * @return
     */
    public static void validatedReportWorldSql(String sql, String dbType) {
        try {
            if (StringUtils.isEmpty(sql)) {
                throw new BusinessException("没有获取到SQL脚本信息，请检查！");
            }
            checLexerToken(sql);
            sql = replaceReportWorldSql(sql);
            //格式化输出
            List<SQLStatement> stmtList = SQLUtils.parseStatements(sql, getDbType(dbType));
            if (StringUtils.isEmpty(stmtList)) {
                throw new BusinessException("没有解析出SQL脚本信息，请检查！");
            }
            for (SQLStatement stmt : stmtList) {
                if (null == stmt || StringUtils.isEmpty(stmt.toString())) {
                    throw new BusinessException("没有解析出SQL脚本信息，请检查！");
                }
                SQLStatementParser statementParser = SQLParserUtils.createSQLStatementParser(stmt.toString(), getDbType(dbType));
                statementParser.parseStatement();
            }
        } catch (Exception ex) {
            throw new BusinessException("解析出现语法错误，请检查！" + ex.getMessage());
        }
    }

    /**
     * druid 格式化（会把中文逗号格式化正确） 这里校验包含这种中文字符的语句，解析出来是否和原来的SQL一致
     *
     * @param sql
     */
    public static void checLexerToken(String sql) {
        if (sql.contains("，") || sql.contains("（") || sql.contains("）")) {
            Lexer lexer = new Lexer(sql);
            for (; ; ) {
                // 解析下一个token
                lexer.nextToken();
                // 获得解析完的token，Token是一个枚举
                Token tok = lexer.token();
                // 逗号，左括号，右括号的durid lexer处理成对应的英文符号
                if (tok == Token.COMMA || tok == Token.LPAREN || tok == Token.RPAREN) {
                    // 取除对应字符和处理过后的比较，是否一致
                    if (!tok.name.equals(String.valueOf(sql.toCharArray()[lexer.pos() - 1]))) {
                        throw new BusinessException("你的脚本中包含了中文的逗号或者中文的括号（除注释以外的地方），请检查！");
                    }
                }
                if (tok == Token.EOF) {
                    break;
                }
            }
        }
    }

    /**
     * 报表世界SQL脚本需要替换的字符
     * 报表结果集和采集的脚本
     *
     * @param sql
     * @return
     */
    public static String replaceReportWorldSql(String sql) {
        sql = sql.replaceAll("\\#\\{TASK_ID\\}", "'1'");
        sql = sql.replaceAll("\\#\\{EAST_ID\\}", "'1'");
        sql = sql.replaceAll("\\$\\{ZHDM\\}", "IN ('1')");
        sql = sql.replaceAll("\\$\\{GJFX\\}", "IN ('1')");
        sql = sql.replaceAll("\\$\\{FUNDID\\}", "IN ('1')");
        sql = sql.replaceAll("\\$\\{PFID\\}", "IN ('1')");
        sql = sql.replaceAll("\\$\\{TA_PF_ID\\}", "IN ('1')");
        sql = sql.replaceAll("\\$\\{FIN_PF_ID\\}", "IN ('1')");
        sql = sql.replaceAll("\\$\\{GZ45_USER\\}", "");
        sql = sql.replaceAll("\\$\\{TA_LOF40_USER\\}", "");
        sql = sql.replaceAll("\\$\\{TA_ZJ40_USER\\}", "");
        sql = sql.replaceAll("\\$\\{TA_HSSS_USER\\}", "");
        sql = sql.replaceAll("\\$\\{TA_ETFTA_USER\\}", "");
        sql = sql.replaceAll("\\$\\{TA_ETF22_USER\\}", "");
        return sql;
    }

    public static void main(String[] args) {
        String sql = "SELECT M1.PF_ID,A.C_IDEN ,M1.BIZ_DATE,A.D_SETT_FACT,A.N_HG_RATE,A.C_ORG_NAME,SUM(NVL(M1.ATTYPE_AMOUNT_OC,0))/*+SUM(NVL(M.ATTYPE_AMOUNT_OC,0))*/ AS QMJE  \n" +
                " from TC_HP_PB_DOM_INFO  M1\n" +
                " LEFT JOIN \n" +
                " TC_TD_PB_DOM_INFO A ON A.PF_ID = M1.PF_ID  AND REGEXP_SUBSTR(M1.SEC_CODE, '[^ ]+', 1, 1)=REGEXP_SUBSTR(A.C_SEC_CODE, '[^ ]+', 1, 1) \n" +
                "  INNER JOIN GET_CPBSJYR T ON A.pF_ID = T.pf_id   ";
        SqlUtil.validatedReportWorldSql(sql, YssDataBaseEnum.MYSQL.getCode());
    }
}
